﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class CashDisbursementManager:ConnectionManager
    {
        #region Add/Update
        public SqlException AddNewDisbursement(DisbursementUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;


                oCommand.CommandText = "INSERT INTO CASH_DISBURSEMENT (DocNum, Reference, Remarks, ReceiptNumber, PostingDate, TINNumber, IsAccount, PayeeCode, PayeeName, AddressOrRemarks, " +
                    "CashAcctCode, CashAcctName, CashAmount, CheckAcctCode, CheckAcctName, CheckAmount, " +
                    "FundTrsfAcctCode, FundTrsfrAcctName, FundTrsfrBank, FundTrsfrReference, FundTrsfrDate, FundTrsfrAmount," +
                    "Total, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                    "(@DocNum, @Reference, @Remarks, @ReceiptNumber, @PostingDate, @TINNumber, @IsAccount, @PayeeCode, @PayeeName, @AddressOrRemarks, " +
                    "@CashAcctCode, @CashAcctName, @CashAmount, @CheckAcctCode, @CheckAcctName, @CheckAmount, " +
                    "@FundTrsfAcctCode, @FundTrsfrAcctName, @FundTrsfrBank, @FundTrsfrReference, @FundTrsfrDate, @FundTrsfrAmount," +
                    "@Total, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";




                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));

                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                //CashAcctCode	nvarchar(50)	Checked
                //CashAcctName	nvarchar(250)	Checked
                //CashAmount	numeric(18, 2)	Checked


                #region Cash Entries
                oCommand.Parameters.Add(new SqlParameter("@CashAcctCode", oUnit.CashGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CashAcctName", oUnit.CashGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CashAmount", oUnit.CashAmount));
                #endregion


                //CheckAcctCode	nvarchar(50)	Checked
                //CheckAcctName	nvarchar(50)	Checked
                //CheckBankName	nvarchar(50)	Checked
                //CheckNumber	nvarchar(50)	Checked
                //CheckDate	nvarchar(50)	Checked
                //CheckAmount	numeric(18, 2)	Checked
                //CheckStatus	nvarchar(50)	Checked

                #region Check Entries
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));

                #endregion


                //FundTrsfAcctCode	nvarchar(50)	Checked
                //FundTrsfrAcctName	nvarchar(50)	Checked
                //FundTrsfrBank	nvarchar(50)	Checked
                //FundTrsfrReference	nvarchar(50)	Checked
                //FundTrsfrDate	datetime	Checked
                //FundTrsfrAmount	numeric(18, 2)	Checked

                #region Fund Transfer
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfAcctCode", oUnit.FundTransferAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAcctName", oUnit.FundTransferAcctName));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrBank", oUnit.FundTransferBank));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrReference", oUnit.FundTransferReference));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrDate", oUnit.FundTransferDate));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAmount", oUnit.FundTransferAmount));
                #endregion

                oCommand.Parameters.Add(new SqlParameter("@Total", oUnit.TotalAmount));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                #region Check Line Items

                string _INSERT_CHECK_ENTRIES_QUERY = "INSERT INTO CASH_DISBURSEMENT_CHECKS (DocNum, CheckAcctCode, CheckAcctName, BankName, CheckNumber, CheckAmount, CheckDate, CheckStatus) VALUES" +
                                                                                                                         " (@DocNum, @CheckAcctCode, @CheckAcctName, @BankName, @CheckNumber, @CheckAmount, @CheckDate, @CheckStatus)";
                foreach (DataRow oRow in oUnit.CheckLineItems.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_CHECK_ENTRIES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                        oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CheckNumber", Convert.ToString(oRow["CheckNumber"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAmount", Convert.ToDouble(oRow["CheckAmount"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckDate", Convert.ToDateTime(oRow["CheckDate"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckStatus", Convert.ToString(oRow["CheckStatus"])));

                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion


                #region Applied Deductions and Expenses

                string _INSERT_DEDUCTION_AND_EXPENSES_QUERY = "INSERT INTO CASH_DISBURSEMENT_DEDUCTIONS (DocNum, Particulars, AcctCode, AcctName, Amount) VALUES" +
                                                                                                                         " (@DocNum, @Particulars, @AcctCode, @AcctName, @Amount)";
                foreach (DataRow oRow in oUnit.DeductionLineItems.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_DEDUCTION_AND_EXPENSES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));

                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion

                #region Applied Invoices

                string _INSERT_INVOICES_QUERY = "INSERT INTO CASH_DISBURSEMENT_VOUCHER (DocNum, DocType, VoucherNum, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @DocType, @VoucherNum, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.VoucherLineItems.Rows)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = _INSERT_INVOICES_QUERY;

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@VoucherNum", oRow["VoucherNum"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", Convert.ToDouble(oRow["AppliedAmount"].ToString())));
                    oCommand.ExecuteNonQuery();


                }

                #endregion

                #region Applied Sales Returns

                string _INSERT_RETURN_QUERY = "INSERT INTO CASH_RECEIPT_RETURNS (DocNum, ReturnNo, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @ReturnNo, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.PurchaseReturnLineItems.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_RETURN_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ReturnNo", oRow["ReturnNo"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));
                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException UpdateDisbursement(DisbursementUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                //CashAcctCode	nvarchar(50)	Checked
                //CashAcctName	nvarchar(250)	Checked
                //CashAmount	numeric(18, 2)	Checked
                //CheckAcctCode	nvarchar(50)	Checked
                //CheckAcctName	nvarchar(50)	Checked
                //CheckBankName	nvarchar(50)	Checked
                //CheckNumber	nvarchar(50)	Checked
                //CheckDate	nvarchar(50)	Checked
                //CheckAmount	numeric(18, 2)	Checked
                //CheckStatus	nvarchar(50)	Checked
                //FundTrsfAcctCode	nvarchar(50)	Checked
                //FundTrsfrAcctName	nvarchar(50)	Checked
                //FundTrsfrBank	nvarchar(50)	Checked
                //FundTrsfrReference	nvarchar(50)	Checked
                //FundTrsfrDate	datetime	Checked
                //FundTrsfrAmount	numeric(18, 2)	Checked
                oCommand.CommandText = "UPDATE CASH_DISBURSEMENT SET Reference=@Reference, Remarks=@Remarks, ReceiptNumber=@ReceiptNumber, PostingDate=@PostingDate, TINNumber=@TINNumber, IsAccount=@IsAccount, PayeeCode=@PayeeCode, PayeeName=@PayeeName, AddressOrRemarks=@AddressOrRemarks, " +
                    "CashAcctCode=@CashAcctCode, CashAcctName=@CashAcctName, CashAmount=@CashAmount, CheckAcctCode=@CheckAcctCode, CheckAcctName=@CheckAcctName, CheckAmount=@CheckAmount, " +
                    "FundTrsfAcctCode=@FundTrsfAcctCode, FundTrsfrAcctName=@FundTrsfrAcctName, FundTrsfrBank=@FundTrsfrBank, FundTrsfrReference=@FundTrsfrReference, FundTrsfrDate=@FundTrsfrDate, FundTrsfrAmount=@FundTrsfrAmount, Total=@Total," +
                    "DocStatus=@DocStatus, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));

                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));


                #region Cash Entries
                oCommand.Parameters.Add(new SqlParameter("@CashAcctCode", oUnit.CashGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CashAcctName", oUnit.CashGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CashAmount", oUnit.CashAmount));
                #endregion

                #region Check Entries
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));
                #endregion

                #region Fund Transfer
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfAcctCode", oUnit.FundTransferAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAcctName", oUnit.FundTransferAcctName));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrBank", oUnit.FundTransferBank));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrReference", oUnit.FundTransferReference));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrDate", oUnit.FundTransferDate));
                oCommand.Parameters.Add(new SqlParameter("@FundTrsfrAmount", oUnit.FundTransferAmount));
                #endregion

                oCommand.Parameters.Add(new SqlParameter("@Total", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();



                #region Check Receipts


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_DISBURSEMENT_CHECKS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                string _INSERT_CHECK_ENTRIES_QUERY = "INSERT INTO CASH_DISBURSEMENT_CHECKS (DocNum, CheckAcctCode, CheckAcctName, BankName, CheckNumber, CheckAmount, CheckDate, CheckStatus) VALUES" +
                                                                                                                        " (@DocNum, @CheckAcctCode, @CheckAcctName, @BankName, @CheckNumber, @CheckAmount, @CheckDate, @CheckStatus)";
                foreach (DataRow oRow in oUnit.CheckLineItems.Rows)
                {
                    try
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = _INSERT_CHECK_ENTRIES_QUERY;

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctCode", oUnit.CheckGLAcctCode));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAcctName", oUnit.CheckGLAcctName));
                        oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CheckNumber", Convert.ToString(oRow["CheckNumber"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckAmount", Convert.ToDouble(oRow["CheckAmount"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckDate", Convert.ToDateTime(oRow["CheckDate"])));
                        oCommand.Parameters.Add(new SqlParameter("@CheckStatus", Convert.ToString(oRow["CheckStatus"])));

                        oCommand.ExecuteNonQuery();
                    }
                    catch { }


                }

                #endregion



                #region Deductions


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_DISBURSEMENT_DEDUCTIONS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                string _INSERT_DEDUCTION_AND_EXPENSES_QUERY = "INSERT INTO CASH_DISBURSEMENT_DEDUCTIONS (DocNum, Particulars, AcctCode, AcctName, Amount) VALUES" +
                                                                                                                         " (@DocNum, @Particulars, @AcctCode, @AcctName, @Amount)";
                foreach (DataRow oRow in oUnit.DeductionLineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        try
                        {
                            oCommand = new SqlCommand();
                            oCommand.Connection = this.Connection;
                            oCommand.CommandText = _INSERT_DEDUCTION_AND_EXPENSES_QUERY;

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));

                            oCommand.ExecuteNonQuery();
                        }
                        catch { }
                    }

                }

                #endregion


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_DISBURSEMENT_VOUCHER WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Applied Invoices

                string _INSERT_INVOICES_QUERY = "INSERT INTO CASH_DISBURSEMENT_VOUCHER (DocNum, DocType, VoucherNum, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @DocType, @VoucherNum, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.VoucherLineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        try
                        {
                            oCommand = new SqlCommand();
                            oCommand.Connection = this.Connection;
                            oCommand.CommandText = _INSERT_INVOICES_QUERY;

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@VoucherNum", oRow["VoucherNum"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));
                            oCommand.ExecuteNonQuery();
                        }
                        catch { }
                    }


                }

                #endregion


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM CASH_DISBURSEMENT_RETURNS WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Applied Sales Returns

                string _INSERT_RETURN_QUERY = "INSERT INTO CASH_DISBURSEMENT_RETURNS (DocNum, ReturnNo, Amount, AppliedAmount) VALUES" +
                                                                                                                         " (@DocNum, @ReturnNo, @Amount, @AppliedAmount)";
                foreach (DataRow oRow in oUnit.PurchaseReturnLineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        try
                        {
                            oCommand = new SqlCommand();
                            oCommand.Connection = this.Connection;
                            oCommand.CommandText = _INSERT_RETURN_QUERY;

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@ReturnNo", oRow["ReturnNo"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));
                            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", Convert.ToDouble(oRow["AppliedAmount"].ToString())));
                            oCommand.ExecuteNonQuery();
                        }
                        catch { }
                    }


                }

                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }
        #endregion


        public DataTable GetDisbursement(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseDisbursementByDocument(string _DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _DocNum = '%' + _DocNum.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNum));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseDisbursementByReceiptNumBer(string _DocumentType, string _ReceiptNumber)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _ReceiptNumber = '%' + _ReceiptNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE DocType=@DocType AND ReceiptNumber LIKE @ReceiptNumber";
            oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", _ReceiptNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocType", _DocumentType));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseDisbursementByCardName(string _CardName)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _CardName = '%' + _CardName.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE CardName LIKE @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseDisbursementByRemarks(string _Remarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _Remarks = '%' + _Remarks.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE Remarks LIKE @Remarks";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", _Remarks));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        //------------------



        public DataTable BrowsePostedDisbursementByDocument(string _DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _DocNum = '%' + _DocNum.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE DocNum LIKE @DocNum AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNum));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowsePostedDisbursementByReceiptNumBer(string _DocumentType, string _ReceiptNumber)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _ReceiptNumber = '%' + _ReceiptNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE DocType=@DocType AND ReceiptNumber LIKE @ReceiptNumber AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", _ReceiptNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocType", _DocumentType));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowsePostedDisbursementByCardName(string _CardName)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _CardName = '%' + _CardName.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE CardName LIKE @CardName AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowsePostedDisbursementByRemarks(string _Remarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _Remarks = '%' + _Remarks.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT WHERE Remarks LIKE @Remarks AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", _Remarks));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        //--------


        public DataTable GetAppliedPurchaseReturn(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT_RETURNS WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAppliedVoucher(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT_VOUCHER WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAppliedDeductions(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CASH_DISBURSEMENT_DEDUCTIONS WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public SqlException PostDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_DISBURSEMENT SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException CloseDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_DISBURSEMENT SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CASH_DISBURSEMENT SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
    }
}
